﻿USE SPKTSNDB
GO

CREATE PROC procGetRoleById
	@pIdRole INT
AS BEGIN
	SELECT * FROM Roles r WHERE r.IdRole = @pIdRole
END
GO

--EXEC procGetRoleById 1
/*==========1. Create Stored procedure procCreateRoles:==========
	- Stored's name: procCreateRoles
	- Execute:	Insert object Roles to table Roles
				Roles's informations has:
					+ IdRole
					+ RoleName
	- Params: 
		+ @pRoleName AS NVARCHAR(50)
	- Author: Dang Quoc Dat
*/
CREATE PROC procCreateRoles
	@pRoleName AS NVARCHAR(30)
AS BEGIN
	INSERT INTO Roles VALUES (@pRoleName)
END
GO

/*==========2. Update Stored procedure procUpdateRoles:==========
	- Stored's name: procUpdateRoles
	- Execute:	Update object Roles from table Roles
				Roles's informations has:
					+ IdRole
					+ RoleName
	- Params:
		+ @pIdRole AS INT 
		+ @pRoleName AS NVARCHAR(50)
	- Author: Dang Quoc Dat
*/

CREATE PROC procUpdateRoles
	@pIdRole AS INT, @pRoleName AS NVARCHAR(30)
AS BEGIN
	UPDATE Roles SET RoleName = @pRoleName WHERE IdRole = @pIdRole
END
GO

/*==========3. Update Stored procedure procDeleteRoles:==========
	- Stored's name: procDeleteRoles
	- Execute:	Delete object Roles from table Roles
				Roles's informations has:
					+ IdRole
					+ RoleName
	- Params:
		+ @pIdRole AS INT 
	- Author: Dang Quoc Dat
*/

CREATE PROC procDeleteRoles
	@pIdRole AS INT
AS BEGIN
	DELETE FROM Roles WHERE IdRole = @pIdRole
END
--Test Store
GO

/*==========4. Create Stored procedure procGetUserByUsername:==========
	- Stored's name: procGetUserByUsername
	- Execute:	Select User's informations
				User's informations has:
					+ Username
					+ Password
					+ Email
					+ CreateDate
					+ Active
					+ IdRole
	- Params: 
		+ @pUsername AS NVARCHAR(30)
	- Author: Dang Quoc Dat
*/
CREATE PROC procGetUserByUsername
	@pUsername AS VARCHAR(30)
AS BEGIN
	SELECT * FROM Users u WHERE u.Username = @pUsername
END
GO

/*==========5. Create Stored procedure procGetUserByIdRole:==========
	- Stored's name: procGetUserByIdRole
	- Execute:	Select User's informations
				User's informations has:
					+ Username
					+ Password
					+ Email
					+ CreateDate
					+ Active
					+ IdRole
	- Params: 
		+ @pIdRole AS INT
	- Author: Dang Quoc Dat
*/
CREATE PROC procGetUserByIdRole
	@pIdRole AS INT
AS BEGIN
	SELECT * FROM Users u WHERE u.IdRole = @pIdRole
END
GO

/*==========6. Create Stored procedure procCreateUser:==========
	- Stored's name: procCreateUser
	- Execute:	Insert object User to table Users
				User's informations has:
					+ Username
					+ Password
					+ Email
					+ CreateDate
					+ Active
					+ IdRole
	- Params: 
		+ @pUsername	AS VARCHAR(30)
		+ @pPassword	AS VARCHAR(16)
		+ @pEmail		AS VARCHAR(30)
	- Author: Dang Quoc Dat
*/
CREATE PROC procCreateUser
	@pUsername AS VARCHAR(30), @pPassword AS VARCHAR(16), @pEmail AS VARCHAR(30)
AS BEGIN
	INSERT INTO Users VALUES (@pUsername, @pPassword, @pEmail, GETDATE(), 1, 3)
END
GO

/*==========7. Create Stored procedure procUpdateUser:==========
	- Stored's name: procUpdateUser
	- Execute:	Update object User in table Users
				User's informations has:
					+ Username
					+ Password
					+ Email
					+ CreateDate
					+ Active
					+ IdRole
	- Params: 
		+ @pUsername	AS VARCHAR(30)
		+ @pPassword	AS VARCHAR(16)
		+ @pActive		AS BIT
		+ @pIdRole		AS INT
	- Author: Dang Quoc Dat
*/
CREATE PROC procUpdateUser
	@pUsername AS VARCHAR(30), @pPassword AS VARCHAR(16), @pActive	AS BIT, @pIdRole AS INT
AS BEGIN
	UPDATE Users
	SET
		[Password] = @pPassword,
		[Active] = @pActive,
		IdRole = @pIdRole
	WHERE UserName = @pUsername 
END
--Test Store
GO

/*==========8. Create Stored procedure procCheckLogin:==========
	- Stored's name: procCheckLogin
	- Execute:	Select User's informations
				Account's informations has:
					+ Username
					+ Password
					+ Email
					+ CreateDate
					+ Active
					+ IdRole
	- Params: 
		+ @pUsername AS NVARCHAR(30)
		+ @pPassword AS NVARCHAR(16)
	- Author: Dang Quoc Dat
*/
CREATE PROC procCheckLogin
	@pUsername AS NVARCHAR(30), @pPassword AS NVARCHAR(16)
AS BEGIN
	SELECT * FROM Users u WHERE u.Username = @pUsername AND u.Password = @pPassword
END
GO


--==========Khoa============--

/*==========1. Create Stored procedure procGetFriendOfUserByUsername:==========
	- Stored's name: procGetFriendOfUserByUsername
	- Execute:	Select friend's information of someone
				Friends's informations has:
					+ Username
					+ MyFriendUsername
					+ IsFriend
					+ CreateTime
	- Params: 
		+ @pUsername AS NVARCHAR(30)
	- Author: Hoang Thai Xuan Khoa
*/
CREATE PROC procGetFriendOfUserByUsername
	@pUsername AS VARCHAR(30)
AS BEGIN
	SELECT * FROM Friends f WHERE f.Username = @pUsername
END
GO


/*==========2. Create Stored procedure procIsFriend:==========
	- Stored's name: procIsFriend
	- Execute:	Select friend relationship's state of someone with their friend
				Friends's informations has:
					+ Username
					+ MyFriendUsername
					+ IsFriend
					+ CreateTime
	- Params: 
		+ @pMe AS VARCHAR(30)
		+ @pmyFriend AS VARCHAR(30)
	- Author: Hoang Thai Xuan Khoa
*/
CREATE PROC procIsFriend
	@pMe AS VARCHAR(30), @pmyFriend AS VARCHAR(30)
AS BEGIN
	SELECT IsFriend FROM Friends f WHERE f.Username = @pMe AND f.MyFriendUsername = @pmyFriend
END
GO


/*==========3. Create Stored procedure procCreateFriend:==========
	- Stored's name: procCreateFriend
	- Execute:	Insert object Friends to table Friends
				Friends's informations has:
					+ Username
					+ MyFriendUsername
					+ IsFriend
					+ CreateTime
	- Params: 
		+ @pMe AS VARCHAR(30)
		+ @pmyFriend AS VARCHAR(30)
	- Author: Hoang Thai Xuan Khoa
*/
CREATE PROC procCreateFriend
	@pMe AS VARCHAR(30), @pmyFriend AS VARCHAR(30)
AS BEGIN
	INSERT INTO Friends VALUES (@pMe, @pmyFriend, 1, GETDATE())
END
Go


/*==========4. Create Stored procedure procDeleteFriendByUsername:==========
	- Stored's name: procDeleteFriendByUsername
	- Execute:	Delete object Friends to table Friends
				Friends's informations has:
					+ Username
					+ MyFriendUsername
					+ IsFriend
					+ CreateTime
	- Params: 
		+ @pMe AS VARCHAR(30)
		+ @pmyFriend AS VARCHAR(30)
	- Author: Hoang Thai Xuan Khoa
*/
CREATE PROC procDeleteFriendByUsername
	@pMe AS VARCHAR(30), @pmyFriend AS VARCHAR(30)
AS BEGIN
	DELETE FROM Friends WHERE Username = @pMe AND MyFriendUsername = @pmyFriend
END
GO


/*==========5. Create Stored procedure procGetMessagesOfUserByUsername:==========
	- Stored's name: procGetMessagesOfUserByUsername
	- Execute:	Select messages of any User by ToUserName and it is never read
				Messages's informations has:
					+ FromUser
					+ ToUserName
					+ [Subject]
					+ Contents
					+ IsFriend
					+ CreateTime
	- Params: 
		+ @pToUserName AS VARCHAR(30)
	- Author: Hoang Thai Xuan Khoa
*/
CREATE PROC procGetMessagesOfUserByUsername
	@pToUserName AS VARCHAR(30)
AS BEGIN
	SELECT * FROM [Messages] m WHERE m.ToUsername = @pToUserName
END
GO


/*==========6. Create Stored procedure procGetMessagesOfUserByDate:==========
	- Stored's name: procGetMessagesOfUserByDate
	- Execute:	Select messages of any User by ToUserName and date and it is never read
				Messages's informations has:
					+ FromUser
					+ ToUserName
					+ [Subject]
					+ Contents
					+ IsFriend
					+ CreateTime
	- Params: 
		+ @pToUserName AS VARCHAR(30)
		+ @pCreateTime AS DATETIME
	- Author: Hoang Thai Xuan Khoa
*/
CREATE PROC procGetMessagesOfUserByDate
	@pToUserName AS VARCHAR(30), @pCreateTime AS DATETIME
AS BEGIN
	SELECT * FROM [Messages] m WHERE m.ToUsername = @pToUserName AND m.CreateTime = @pCreateTime
END
GO


/*==========7. Create Stored procedure procCreateMessage:==========
	- Stored's name: procCreateMessage
	- Execute:	Insert object Messages to table Messages
				Messages's informations has:
					+ FromUser
					+ ToUserName
					+ [Subject]
					+ Contents
					+ IsFriend
					+ CreateTime
	- Params: 
		+ @pfromUser AS VARCHAR(30)
		+ @ptoUser AS VARCHAR(30)
		+ @pSubject AS NVARCHAR(MAX)
		+ @pContents AS NVARCHAR(MAX)
	- Author: Hoang Thai Xuan Khoa
*/
CREATE PROC procCreateMessage
	@pfromUser AS VARCHAR(30), @ptoUser AS VARCHAR(30), @pSubject AS NVARCHAR(MAX),@pContents AS NVARCHAR(MAX)
AS BEGIN
	INSERT INTO [Messages] VALUES (@pfromUser, @ptoUser, @pSubject, @pContents, GETDATE())
END
GO

/*==========8. Create Stored procedure procDeleteMessage:==========
	- Stored's name: procDeleteMessage
	- Execute:	Delete object Messages to table Messages
				Messages's informations has:
					+ FromUser
					+ ToUserName
					+ [Subject]
					+ Contents
					+ IsFriend
					+ CreateTime
	- Params: 
		+ @pToUser AS VARCHAR(30)
		+ @pCreateTime AS DATETIME
	- Author: Hoang Thai Xuan Khoa
*/
CREATE PROC procDeleteMessage
	@pToUser AS VARCHAR(30), @pCreateTime AS DATETIME
AS BEGIN
	DELETE FROM [Messages] WHERE ToUserName = @pToUser AND CreateTime = @pCreateTime
END
GO


/*==========9. Create Stored procedure procGetListPostsOfUserByUsername:==========
	- Stored's name: procGetListPostsOfUserByUsername
	- Execute:	Select posts of any User by Username
				Posts's informations has:
					+ IdPost
					+ Username
					+ Contents
					+ Privacy
					+ CreateTime
	- Params: 
		+ @pUsername AS VARCHAR(30)
	- Author: Hoang Thai Xuan Khoa
*/
CREATE PROC procGetListPostsOfUserByUsername
	@pUsername AS VARCHAR(30)
AS BEGIN
	SELECT * FROM Posts p WHERE p.Username = @pUsername
END
GO


/*==========10. Create Stored procedure procGetListPostOfUserByDate:==========
	- Stored's name: procGetListPostOfUserByDate
	- Execute:	Select posts of any User by date
				Posts's informations has:
					+ IdPost
					+ Username
					+ Contents
					+ Privacy
					+ CreateTime
	- Params: 
		+ @pUsername AS VARCHAR(30)
		+ @pCreateTime AS DATETIME
	- Author: Hoang Thai Xuan Khoa
*/
CREATE PROC procGetListPostOfUserByDate
	@pUsername AS VARCHAR(30), @pCreateTime AS DATETIME
AS BEGIN
	SELECT * FROM Posts p WHERE p.Username = @pUsername AND p.CreateTime = @pCreateTime
END
GO


/*==========11. Create Stored procedure procGetListPostOfUserByPrivacy:==========
	- Stored's name: procGetListPostOfUserByPrivacy
	- Execute:	Select posts of any User by privacy
				Posts's informations has:
					+ IdPost
					+ Username
					+ Contents
					+ Privacy
					+ CreateTime
	- Params: 
		+ @pUsername AS VARCHAR(30)
		+ @pPrivacy AS BIT
	- Author: Hoang Thai Xuan Khoa
*/
CREATE PROC procGetListPostOfUserByPrivacy
	@pUsername AS VARCHAR(30), @pPrivacy AS BIT
AS BEGIN
	SELECT * FROM Posts p WHERE p.Username = @pUsername AND p.Privacy = @pPrivacy
END
GO

/*==========12. Create Stored procedure procGetPostOfUserByIdPost:==========
	- Stored's name: procGetPostOfUserByIdPost
	- Execute:	Select posts of any User by IdPost
				Posts's informations has:
					+ IdPost
					+ Username
					+ Contents
					+ Privacy
					+ CreateTime
	- Params: 
		+ @pUsername AS VARCHAR(30)
		+ @pIdPost AS INT
	- Author: Hoang Thai Xuan Khoa
*/
CREATE PROC procGetPostOfUserByIdPost
	@pUsername AS VARCHAR(30), @pIdPost AS INT
AS BEGIN
	SELECT * FROM Posts p WHERE p.Username = @pUsername AND p.IdPost = @pIdPost
END
GO


/*==========13. Create Stored procedure procCreatePost:==========
	- Stored's name: procCreatePost
	- Execute:	Insert object Posts to table Posts
				Posts's informations has:
					+ IdPost
					+ Username
					+ Contents
					+ Privacy
					+ CreateTime
	- Params: 
		+ @pUsername AS VARCHAR(30)
		+ @pContents AS VARCHAR(MAX)
		+ @pPrivacy AS BIT
	- Author: Hoang Thai Xuan Khoa
*/
CREATE PROC procCreatePost
	@pUsername AS VARCHAR(30), @pContents AS VARCHAR(MAX), @pPrivacy AS BIT
AS BEGIN
	INSERT INTO Posts VALUES (@pUsername, @pContents, @pPrivacy, GETDATE())
END
GO


/*==========14. Create Stored procedure procDeletePost:==========
	- Stored's name: procDeletePost
	- Execute:	Delete object Posts to table Posts
				Posts's informations has:
					+ IdPost
					+ Username
					+ Contents
					+ Privacy
					+ CreateTime
	- Params: 
		+ @pIdPost AS INT
	- Author: Hoang Thai Xuan Khoa
*/
CREATE PROC procDeletePost
	@pIdPost AS INT
AS BEGIN
	DELETE FROM Posts WHERE IdPost = @pIdPost
END
GO


/*==========15. Create Stored procedure procGetListCommentsOfPostByIdPost:==========
	- Stored's name: procGetListCommentsOfPostByIdPost
	- Execute:	Select comments of any User by IdPost
				Comments's informations has:
					+ IdComment
					+ Username
					+ Contents
					+ Privacy
					+ IdPost
					+ CreateTime
	- Params: 
		+ @pIdPost AS INT
	- Author: Hoang Thai Xuan Khoa
*/
CREATE PROC procGetListCommentsOfPostByIdPost
	@pIdPost AS INT
AS BEGIN
	SELECT * FROM Comments c WHERE c.IdPost = @pIdPost
END
GO


/*==========16. Create Stored procedure procGetListCommentsOfUsersAndPost:==========
	- Stored's name: procGetListCommentsOfUsersAndPost
	- Execute:	Select comments of any User by Username and IdPost
				Comments's informations has:
					+ IdComment
					+ Username
					+ Contents
					+ Privacy
					+ IdPost
					+ CreateTime
	- Params: 
		+ @pUsername AS VARCHAR(30)
		+ @pIdPost AS INT
	- Author: Hoang Thai Xuan Khoa
*/
CREATE PROC procGetListCommentsOfUsersAndPost
	@pUsername AS VARCHAR(30), @pIdPost AS INT
AS BEGIN
	SELECT * FROM Comments c WHERE c.Username = @pUsername AND c.IdPost = @pIdPost
END
GO


/*==========17. Create Stored procedure procCreateComment:==========
	- Stored's name: procCreateComment
	- Execute:	Insert object Comments to table Comments
				Comments's informations has:
					+ IdComment
					+ Username
					+ Contents
					+ Privacy
					+ IdPost
					+ CreateTime
	- Params: 
		+ @pUsername AS VARCHAR(30)
		+ @pContents AS VARCHAR(MAX)
		+ @pPrivacy AS BIT
		+ @pIdPost AS INT
	- Author: Hoang Thai Xuan Khoa
*/
CREATE PROC procCreateComment
	@pUsername AS VARCHAR(30), @pContents AS VARCHAR(MAX), @pPrivacy AS BIT, @pIdPost AS INT
AS BEGIN
	INSERT INTO Comments VALUES (@pUsername, @pContents, @pPrivacy, @pIdPost, GETDATE())
END
GO

/*==========18. Create Stored procedure procDeleteComment:==========
	- Stored's name: procDeleteComment
	- Execute:	Delete object Comments to table Comments
				Comments's informations has:
					+ IdComment
					+ Username
					+ Contents
					+ Privacy
					+ IdPost
					+ CreateTime
	- Params: 
		+ @pIdComment AS INT
	- Author: Hoang Thai Xuan Khoa
*/
CREATE PROC procDeleteComment
	@pIdComment AS INT
AS BEGIN
	DELETE FROM Comments WHERE IdComment = @pIdComment
END
GO

--19.
CREATE PROC procGetPostByIdPost
@pIdPost INT
AS BEGIN
SELECT * FROM Posts  WHERE IdPost=@pIdPost
END